import os
from datetime import datetime
import pandas as pd
from PySide6.QtCore import QStandardPaths
from PySide6.QtWidgets import QWidget, QVBoxLayout, QPushButton, QFileDialog, QMessageBox
from util import dbUtil


def dataImport(self):
    # 默认打开文档目录
    documents_path = QStandardPaths.writableLocation(QStandardPaths.StandardLocation.DocumentsLocation)

    # 打开文件选择对话框
    file_path, _ = QFileDialog.getOpenFileName(None, '选择导入的数据', documents_path, 'Excel文件 (*.xlsx *.xls)')

    if file_path:
        # 获取文件名（不包括扩展名）
        file_name = os.path.basename(file_path)
        file_name, _ = os.path.splitext(file_name)

        # 连接到数据库
        conn = dbUtil.get_db_conn()
        if not conn:
            QMessageBox.critical(self, "连接失败", "无法连接到数据库。")
            return

        try:
            with conn.cursor() as cursor:
                """
                2.1 一个xlsx文件可能会有多个sheet，每个sheet的为一个矿井，获取sheet名称作为矿井名，加入到数据库cq_well中
                2.2 每一个sheet做单独处理，新建$well_itemName_fileName$表，用来存储此矿井中的数据
                """

                xls = pd.ExcelFile(file_path)
                sheet_names = xls.sheet_names

                # 获取当前的日期和时间
                current_datetime = datetime.now()
                current_datetime_truncated = current_datetime.replace(microsecond=0)

                # 暂时指定用户为admin，后期改为登录用户
                user = "admin"

                # 插入到 cq_item 表
                insert_cq_item = "INSERT INTO cq_item (项目名称, 导入用户, 导入时间) VALUES (%s, %s, %s)"
                cursor.execute(insert_cq_item, (file_name, user, current_datetime_truncated))
                item_id = cursor.lastrowid  # 获取插入的项目ID

                for well_name in sheet_names:
                    # 读取当前工作表
                    df = pd.read_excel(xls, sheet_name=well_name, skiprows=2)  # 跳过第一行标题和第二行单位

                    # 插入到 cq_well 表
                    insert_cq_well = "INSERT INTO cq_well (矿井名称, 项目ID) VALUES (%s, %s)"
                    cursor.execute(insert_cq_well, (well_name, item_id))
                    well_id = cursor.lastrowid  # 获取插入的矿井ID

                    table_name = createTable(file_name, well_name)  # 创建表

                    for index, row in df.iterrows():
                        depth = row.iloc[0]
                        gas_content = row.iloc[1]
                        rock_type = row.iloc[2]
                        brittleness_index = row.iloc[3]
                        critical_strain_rate = row.iloc[4]
                        stress_difference_coefficient = row.iloc[5]
                        fracture_pressure = row.iloc[6]
                        compressive_strength = row.iloc[7]
                        permeability = row.iloc[8]
                        sweetness = row.iloc[9]

                        # 插入到动态生成的矿井数据表
                        insert_well_data = f"""
                            INSERT INTO `{table_name}` (矿井ID, 井深, 含气量, 岩性, 脆性指数,
                                                        临界应变能释放率, 地应力差异系数, 破裂压力,
                                                        抗压强度, 渗透率, 甜度)
                            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                        """
                        cursor.execute(insert_well_data,
                                       (well_id, depth, gas_content, rock_type, brittleness_index,
                                        critical_strain_rate, stress_difference_coefficient,
                                        fracture_pressure, compressive_strength, permeability, sweetness))

                # 显示成功消息
                QMessageBox.information(None, "导入成功", "数据已成功导入到数据库。")

        except Exception as e:
            if conn:
                conn.rollback()  # 回滚事务
            error_message = str(e)
            print(error_message)
            QMessageBox.critical(None, "导入失败", f"发生错误: {error_message}")
            return 0

        finally:
            if conn:
                dbUtil.close_db_conn(conn)


def createTable(item_name, well_name):

    # 连接到数据库
    conn = dbUtil.get_db_conn()
    if not conn:
        QMessageBox.critical(None, "连接失败", "无法连接到数据库。")
        return

    try:
        with conn.cursor() as cursor:
            f_table_name = f"well_{item_name}_{well_name}"
            sql = f"""CREATE TABLE IF NOT EXISTS `{f_table_name}` (
                矿井数据ID INT AUTO_INCREMENT PRIMARY KEY,
                矿井ID INT NOT NULL,
                井深 DECIMAL(15, 5) NOT NULL COMMENT '单位：m',
                含气量 DECIMAL(25, 15) NOT NULL COMMENT '单位：cm3/g',
                岩性 DECIMAL(15, 5) NOT NULL,
                脆性指数 DECIMAL(15, 9) NOT NULL COMMENT '单位：%',
                临界应变能释放率 DECIMAL(25, 15) NOT NULL,
                地应力差异系数 DECIMAL(25, 15) NOT NULL,
                破裂压力 DECIMAL(25, 15) NOT NULL COMMENT '单位：MPa',
                抗压强度 DECIMAL(25, 15) NOT NULL COMMENT '单位：MPa',
                渗透率 DECIMAL(25, 15) NOT NULL COMMENT '单位：mD',
                甜度 DECIMAL(25, 15) NOT NULL,
                FOREIGN KEY (矿井ID) REFERENCES cq_well(矿井ID)
            );"""
            cursor.execute(sql)
            return f_table_name

    except Exception as e:
        if conn:
            conn.rollback()  # 回滚事务
        error_message = str(e)
        print(error_message)
        QMessageBox.critical(None, "创建失败", f"发生错误: {error_message}")
        return 0

    finally:
        if conn:
            dbUtil.close_db_conn(conn)
